﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite ;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Security.Cryptography;
using System.Linq;
using Handrise.Mobile.BasicModal.Config;

namespace Handrise.Mobile.BasicModal.DataLayer
{
    public class DataService 
    {
        private SQLiteConnection connection;

        //private Type type;
        //private string tableName;
        private string currentDB;
        //private Dictionary<string, string> ColumnMappingDic = new Dictionary<string, string>();
        //private string keyName;
        //private List<string> encryptionPropertyList = new List<string>();
        private bool isTrans = false ;

        ////配置模块接口对象
        private ConfigService config ;
        ////日志模块接口对象
        //private static ILog log = (ILog)MobileCore.GetService(typeof(ILog));

        //是否支持日志的标记

        private bool isSupportLog = false;

        //实体映射缓存
        private List<EntityMapping> mappingList = new List<EntityMapping>();
        //实体映射结构
        public class EntityMapping
        {
            public Type type;
            public string tableName;
            public Dictionary<string, ColumnAttribute> ColumnMappingDic;
            public string keyName;
            //public List<string> encryptionPropertyList;
        }

        public class ColumnAttribute
        {
            public string ColumnName{get;set;}           //表中对应的列名
            public string AttributeName { get; set; }    //属性对应名称
            public int Order { get; set; }               //表中对应的字段所在表的顺序
            public bool IsEncryption { get; set; }       //是否需要加密
        }

        private List<TransactionCache> transactionCacheList = new List<TransactionCache>();
        public struct TransactionCache
        {
            public SQLiteConnection connection;
            public SQLiteCommand command;
            public SQLiteTransaction transaction;
        }

        private Dictionary<string, ConnectionInfo> connectionCache = new Dictionary<string, ConnectionInfo>();
        public struct ConnectionInfo
        {
            public SQLiteConnection connection;
            public SQLiteCommand command;
        }

        //密钥
        private string key = "Handrise";
        /// <summary>
        /// 设置密钥
        /// </summary>
        /// <param name="key">密钥，且必须为8位。</param>
        /// <returns></returns>
        public bool SetKey(string _key)
        {
            if (string.IsNullOrEmpty(key))
            {
                return false;
            }

            if (key.Length != 8)
            {
                return false;
            }
            else
            {
                key = _key;
            }

            return true;
        }

        private string GetLocalPath()
        {
            string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);

            if (path.StartsWith("file:\\"))
                return path.Remove(0, 6);
            else
                return path;
        }

        /// <summary>
        /// 构造函数

        /// </summary>
        public DataService()
        {
            //string dbName = string.Empty;
            string dbPath = string.Empty;
            string dbPassword = string.Empty;

            config = new ConfigService();

            //获取配置文件(app.config)中是否支持日志的标记
            string flag = string.Empty;
            if (config != null)
            {
                flag = config.GetParameter("Log", "IsSupport");
            }
            else
            {
                return;
            }

            if (string.IsNullOrEmpty(flag))
            {
                isSupportLog = false;
            }
            else if (flag == "Yes")
            {
                isSupportLog = true;
            }
            else if (flag == "No")
            {
                isSupportLog = false;
            }

            //获取配置文件(app.config)中数据源默认值

            string dbName = config.GetParameter("Database", "Default");
            CreateNewConnection(dbName);
        }

        /// <summary>
        /// 析构函数
        /// </summary>
        ~DataService()
        {
            if (connection != null)
            {
                //关闭SqlCeConnection类对象

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }  
        }

        /// <summary>
        /// 创建新的连接
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        private bool CreateNewConnection(string dbName)
        {
            //获取配置文件(app.config)中数据源默认值
            if (string.IsNullOrEmpty(dbName))
            {
                return false;
            }

            try
            {
                //定义Connection类对象并打开
                connection = new SQLiteConnection();
                connection.ConnectionString = "Data Source=" + GetLocalPath() + "\\" + config.GetParameter("Database", dbName);

                if (connection.State == ConnectionState.Closed)
                {
                    //SqlLite在Open的时候居然去调用app.config中的内容，但app.config是配置模块的基本文件，程序在运行时报
                    //"System.Transactions.Diagnostics.DiagnosticTrace"类型初始值设定项引发异常，但在调试的情况下居然又可以
                    //不得原因，因此修改app.config为appconfig.xml,问题消除
                    connection.Open();
                }

                //定义SqlCeCommand类对象
                currentDB = dbName; 

                ConnectionInfo connectionInfo = new ConnectionInfo() ;
                connectionInfo.connection = connection ;
                connectionCache.Add(dbName, connectionInfo);

                return true;
            }
            catch (Exception e)
            {
                return false ;
            }
        }

        /// <summary>
        /// 获取实体类型属性信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        private EntityMapping GetEntityTypeInfo(Type type)
        {
            //lock (this)
            {
                EntityMapping entityMapping = mappingList.Where(t => t.type == type).FirstOrDefault();

                if (entityMapping != null)
                    return entityMapping;
                else
                {
                    return SetMapping(type);
                }               
            }
        }

        /// <summary>
        /// 获取T映射关系
        /// </summary>
        private EntityMapping SetMapping(Type type)
        {
            string tableName = "";
            string keyName = "";
            Dictionary<string, ColumnAttribute> columnMappingDic = new Dictionary<string, ColumnAttribute>();
            //List<string> encryptionPropertyList = new List<string>();


            //通过Attribute方式获取映射关系
            object[] attributeArr = type.GetCustomAttributes(typeof(EntityMappingAttribute), false);
            EntityMappingAttribute attributeMapp;
            if (attributeArr != null && attributeArr.Length > 0)
            {
                attributeMapp = (EntityMappingAttribute)attributeArr[0];
                tableName = attributeMapp.TableName;
            }
            else
            {
                tableName = type.Name;
            }

            //获取对应表结构,并设置对应的字段位置
            DataTable tableStruct = GetTableStruct(tableName);


            //设置属性和字段的映射关系
            foreach (PropertyInfo proInfo in type.GetProperties())
            {
                attributeArr = proInfo.GetCustomAttributes(typeof(EntityMappingAttribute), false);
                if (attributeArr != null && attributeArr.Length > 0)
                {
                    attributeMapp = (EntityMappingAttribute)attributeArr[0];
                    if (attributeMapp.IsKey)
                    {
                        //如果主键设置的对应的字段则使用字段名称，否则使用实体属性名称
                        if (attributeMapp.ColumnName != "")
                            keyName = attributeMapp.ColumnName;
                        else
                            keyName = proInfo.Name;
                    }

                    ColumnAttribute columnAttribute = new ColumnAttribute();
                    columnAttribute.AttributeName = proInfo.Name;
                    //设置数据库表中的字段列名
                    if (attributeMapp.ColumnName != null)
                    {
                        columnAttribute.ColumnName = attributeMapp.ColumnName;
                    }
                    //设置是否需要加密
                    if (attributeMapp.IsEncryption && proInfo.PropertyType.Name.ToString() == "String")
                    {
                        columnAttribute.IsEncryption = true;
                    }

                    //设置字段在表中的顺序
                    DataRow[] rows = tableStruct.Select("ColumnName = '" + proInfo.Name + "'");
                    if (rows.Count() == 1)
                    {
                        columnAttribute.Order = tableStruct.Rows.IndexOf(rows[0]);
                    }

                    columnMappingDic.Add(proInfo.Name, columnAttribute);

                }
            }

            ////从配置文件(app.config)中获取映射关系
            //if (config != null)
            //{
            //    if (!string.IsNullOrEmpty(config.GetParameter(type.Name.ToString(), "TableName")))
            //    {
            //        tableName = config.GetParameter(type.Name.ToString(), "TableName");
            //        string[] arrCol = config.GetParameter(type.Name.ToString(), "ColumnName").Split(',');
            //        string[] arrPro = config.GetParameter(type.Name.ToString(), "PropertyName").Split(',');
            //        if (arrCol.Length == arrPro.Length)
            //        {
            //            ColumnMappingDic.Clear();
            //            for (int i = 0; i < arrCol.Length; i++)
            //            {
            //                ColumnMappingDic.Add(arrCol[i], arrPro[i]);
            //            }
            //        }
            //        keyName = config.GetParameter(type.Name.ToString(), "KeyName");
            //    }
            //}

            //如果主键没有设置，则使用表名+ID
            if (keyName == "")
                keyName = tableName + "ID";




            //向缓存中插入映射关系
            EntityMapping mapp = new EntityMapping();
            mapp.type = type;
            mapp.tableName = tableName;
            mapp.ColumnMappingDic = columnMappingDic;
            mapp.keyName = keyName;
            //mapp.encryptionPropertyList = encryptionPropertyList;
            mappingList.Add(mapp);

            return mapp;
        }

        /// <summary>
        /// 获取主键键值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        private string GetKeyValue<T>(T entity)
        {
            string retStr = string.Empty;

            EntityMapping entityMapping = GetEntityTypeInfo(typeof(T));

            PropertyInfo[] propertyInfos = entity.GetType().GetProperties();

            if (string.IsNullOrEmpty(entityMapping.keyName))
            {
                retStr = string.Empty;
            }
            else
            {
                for (int i = 0; i < propertyInfos.Length; i++)
                {
                    if (entityMapping.keyName == propertyInfos[i].Name)
                    {
                        retStr = Convert.ToString(propertyInfos[i].GetValue(entity, null));
                        break;
                    }
                }
            }

            return retStr;
        }

        /// <summary>
        /// 枚举->object
        /// </summary>
        /// <param name="enumType"></param>
        /// <param name="enumItem"></param>
        /// <returns></returns>
        private object EnumToObject(Type enumType, string enumItem)
        {
            if (Enum.IsDefined(enumType, enumItem))
            {
                return Enum.Parse(enumType, enumItem, true);
            }
            return null;
        }

        /// <summary>
        /// 数据向实体赋值
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="target"></param>
        private void ReaderToObject(IDataReader reader, object target, EntityMapping entityMapping)
        {
            //EntityMapping entityMapping = GetEntityTypeInfo(target.GetType());

            //获取所有实体的属性
            PropertyInfo[] propertyInfos = entityMapping.type.GetProperties();

            int index = 0;
            //遍历实体属性并赋值
            foreach (PropertyInfo item in propertyInfos)
            {
                //获取对应属性的数据库字段名称,不存在则取下一个属性信息
                if (!entityMapping.ColumnMappingDic.ContainsKey(item.Name))
                    continue;

                ColumnAttribute columnAttribute = entityMapping.ColumnMappingDic[item.Name];
                string fieldName;
                if (columnAttribute != null)
                {
                    fieldName = columnAttribute.ColumnName;
                }
                else
                {
                    continue;
                }

                //获取实体对应属性的内容
                try
                {
                    object value = reader[columnAttribute.Order];
                    if (value == System.DBNull.Value)
                        continue;


                    //内容如果经过加密，则进行解密
                    if (columnAttribute.IsEncryption)
                    {
                        value = Decrypt((string)value, key);
                    }

                    //设置特殊内容
                    //Boolean类型
                    if (item.PropertyType.FullName.Contains("Boolean"))
                    {
                        if (value.ToString() == "1")
                            value = true;
                        else
                            value = false;
                    }

                    //枚举类型
                    if (item.PropertyType.IsEnum)
                    {
                        value = EnumToObject(item.PropertyType, (string)value);
                    }


                    item.SetValue(target, value, null);
                }
                catch(Exception e)
                {
                }
            }
        }

        /// <summary> 
        /// 进行DES加密。 
        /// </summary> 
        /// <param name="pToEncrypt">要加密的字符串。</param> 
        /// <param name="sKey">密钥，且必须为8位。</param> 
        /// <returns>以Base64格式返回的加密字符串。</returns> 
        public static string Encrypt(string pToEncrypt, string sKey)
        {
            using (DESCryptoServiceProvider des = new DESCryptoServiceProvider())
            {
                byte[] inputByteArray = Encoding.UTF8.GetBytes(pToEncrypt);
                des.Key = ASCIIEncoding.ASCII.GetBytes(sKey);
                des.IV = ASCIIEncoding.ASCII.GetBytes(sKey);
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                using (CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(), CryptoStreamMode.Write))
                {
                    cs.Write(inputByteArray, 0, inputByteArray.Length);
                    cs.FlushFinalBlock();
                    cs.Close();
                }
                string str = Convert.ToBase64String(ms.ToArray());
                ms.Close();
                return str;
            }
        }

        /// <summary> 
        /// 进行DES解密。 
        /// </summary> 
        /// <param name="pToDecrypt">要解密的以Base64</param> 
        /// <param name="sKey">密钥，且必须为8位。</param> 
        /// <returns>已解密的字符串。</returns> 
        public static string Decrypt(string pToDecrypt, string sKey)
        {
            byte[] inputByteArray = Convert.FromBase64String(pToDecrypt);
            using (DESCryptoServiceProvider des = new DESCryptoServiceProvider())
            {
                des.Key = ASCIIEncoding.ASCII.GetBytes(sKey);
                des.IV = ASCIIEncoding.ASCII.GetBytes(sKey);
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                using (CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write))
                {
                    cs.Write(inputByteArray, 0, inputByteArray.Length);
                    cs.FlushFinalBlock();
                    cs.Close();
                }
                string str = Encoding.UTF8.GetString(ms.ToArray(), 0, ms.ToArray().Length);
                ms.Close();
                return str;
            }
        }
    

        /// <summary>
        /// 通过SQL获取List<T>中首元素
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public T GetEntityBySQL<T>(string sql)
        {
            if (string.IsNullOrEmpty(sql))
            {
                return default(T);
            }

            List<T> retLis = new List<T>();

            retLis = ExecuteSql<T>(sql);
            if (retLis == null)
            {
                return default(T);
            }
            else if (retLis.Count == 0)
            {
                return default(T);
            }
            else
            {
                return retLis[0];
            }
        }

        /// <summary>
        /// 插入数据，单个实体。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns>插入的个数</returns>
        public int Add<T>(T entity)
        {
            if (entity == null)
            {
                return 0;
            }

            List<T> entityLis = new List<T>();
            entityLis.Add(entity);

            return Add(entityLis);
        }

        /// <summary>
        /// 插入数据，实体列表，支持事务。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">需要插入的实体数据</param>
        /// <returns>影响的行数</returns>
        public int Add<T>(List<T> list)
        {
            if (list == null || connection == null)
            {
                return 0;
            }

            if (connection.State == ConnectionState.Closed)
            {
                return 0;
            }

            //获取映射关系
            EntityMapping entityMapping = GetEntityTypeInfo(typeof(T));

            int addCount = 0;
            //设置属性
            List<string> sqls = new List<string>();
            foreach (T entity in list)
            {


                string tId = string.Empty;
                StringBuilder sbField = new StringBuilder();
                StringBuilder sbValue = new StringBuilder();
                PropertyInfo[] propertyInfos = entity.GetType().GetProperties();
                Dictionary<string,object> parameters = new Dictionary<string,object>() ;

                sbField.Append(" insert into [");
                sbField.Append(entityMapping.tableName.Trim());
                sbField.Append("] (");
                sbValue.Append(" values(");
                foreach (PropertyInfo pInfo in propertyInfos)
                {
                    //获取实体对应的字段名称,如果没有设置则认为该属性没有对应数据库字段
                    if(!entityMapping.ColumnMappingDic.ContainsKey(pInfo.Name.Trim()))
                        continue  ;

                    ColumnAttribute columnAttribute = entityMapping.ColumnMappingDic[pInfo.Name.Trim()];
                    string filedName = columnAttribute.ColumnName ;

                    //内容是否需要加密
                    object value ;
                    if (columnAttribute.IsEncryption)
                    {
                        value = Encrypt((string)pInfo.GetValue(entity, null),key);
                    }
                    else
                    {
                        value = pInfo.GetValue(entity, null);
                    }

                    parameters.Add(filedName,value) ;

                    sbField.Append("[");
                    sbField.Append(filedName);
                    sbField.Append("]");
                    sbField.Append(",");
                    //sbValue.Append("'");
                    sbValue.Append("@" + filedName);
                    sbValue.Append(",");
               }

                addCount += ExecuteSql(sbField.ToString().Trim(',') + ")" + sbValue.ToString().Trim(',') + ")", parameters);
            }

            return addCount;

        }

        /// <summary>
        /// 创建command的参数
        /// </summary>
        /// <param name="parameters"></param>
        private void CreateParameters(SQLiteCommand cmd,Dictionary<string, object> parameters)
        {
            cmd.Parameters.Clear();
            foreach (KeyValuePair<string, object> item in parameters)
            {
                SQLiteParameter parameter;
                if (item.Value == null)
                {
                    parameter = cmd.Parameters.Add(item.Key, DbType.String);
                }
                else
                {
                    switch (item.Value.GetType().Name)
                    {
                        case "String":
                            parameter = cmd.Parameters.Add(item.Key, DbType.String);
                            break;
                        case "DateTime":
                            parameter = cmd.Parameters.Add(item.Key, DbType.DateTime);
                            break;
                        case "Double":
                            parameter = cmd.Parameters.Add(item.Key, DbType.Double);
                            break;
                        case "Boolean":
                            parameter = cmd.Parameters.Add(item.Key, DbType.Boolean);
                            break;
                        case "Byte[]":
                            parameter = cmd.Parameters.Add(item.Key, DbType.Binary);
                            break;
                        default:
                            parameter = cmd.Parameters.Add(item.Key, DbType.String);
                            break;
                    }
                }
                parameter.Value = item.Value;

            }
        }

       /// <summary>
        /// 执行SQL语句,带参数
       /// </summary>
       /// <param name="sql"></param>
       /// <param name="parameters"></param>
       /// <returns></returns>
        public int ExecuteSql(string sql, Dictionary<string, object> parameters)
        {
            SQLiteCommand command = new SQLiteCommand();
            try
            {
                command.Connection = connection;
                command.CommandText = sql;
                if(parameters != null)
                    CreateParameters(command, parameters);
                return command.ExecuteNonQuery();
            }
            catch(Exception e)
            {
                if (isTrans)
                    throw;
                return 0;
            }

        }

        /// <summary>
        /// 执行1条SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteSql(string sql)
        {
            SQLiteCommand command = new SQLiteCommand(); 
            try
            {
                command.Connection = connection;
                command.CommandText = sql;
                return command.ExecuteNonQuery();

            }
            catch (Exception)
            {
                if (isTrans)
                    throw;
                return 0;
            }
        }

        /// <summary>
        /// 执行多条SQL语句
        /// </summary>
        /// <param name="sqlStringList">多条SQL语句</param>		
        public int ExecuteSql(List<String> sqlStringList)
        {
            SQLiteCommand command = new SQLiteCommand(); 
            try
            {
                command.Connection = connection;
                int count = 0;
                for (int n = 0; n < sqlStringList.Count; n++)
                {
                    string strsql = sqlStringList[n];
                    if (strsql.Trim().Length <= 1) continue;
                    command.CommandText = strsql;
                    count += command.ExecuteNonQuery();
                }
                return count;
            }
            catch (Exception)
            {
                if(isTrans)
                    throw;
                return 0;
            }


        }
        

        ///<summary>
        ///删除数据，通过主键键值。
        ///</summary>
        ///<param name="keyValue"></param>
        ///<returns></returns>
        public int Delete<T>(object keyValue)
        {
            if (keyValue == null)
            {
                return 0 ;
            }

            List<object> keys = new List<object>();

            keys.Add(keyValue);
            return Delete<T>(keys);
        }

        /// <summary>
        /// 删除数据，通过主键键值列表。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns>影响的行数</returns>
        public int Delete<T>(List<object> keys)
        {
            if (keys == null || connection == null)
            {
                return 0;
            }


            if (connection.State == ConnectionState.Closed)
            {
                return 0;
            }

            //获取映射关系
            EntityMapping entityMapping = GetEntityTypeInfo(typeof(T));

            if (string.IsNullOrEmpty(entityMapping.tableName))    //判断表
            {
                return 0;
            }
            else if (string.IsNullOrEmpty(entityMapping.keyName)) //判断主键
            {
                return 0;
            }


            int count = 0;
            foreach (object keyValue in keys)
            {
                //定义SQL语句
                string sql = string.Format("delete from {0} where [{1}] = @{2}", entityMapping.tableName, entityMapping.keyName, entityMapping.keyName);

                Dictionary<string, object> parameters = new Dictionary<string, object>();
                parameters.Add(entityMapping.keyName, keyValue);

                count += ExecuteSql(sql, parameters);


            }
            return count;
        }

        /// <summary>
        /// 删除数据，通过条件及参数化方式。
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int Delete<T>(string condition, Dictionary<string, object> parameters)
        {
            string sql = string.Empty;

            //获取映射关系
            EntityMapping entityMapping = GetEntityTypeInfo(typeof(T));

            if (string.IsNullOrEmpty(condition))
            {
                sql = string.Format("delete from {0}", entityMapping.tableName);
            }
            else
            {
                sql = string.Format("delete from {0} where {1}", entityMapping.tableName, condition);
            }

            return ExecuteSql(sql, parameters);
        }


        /// <summary>
        /// 通过SQL获取List<T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> ExecuteSql<T>(string sql)
        {
            if (string.IsNullOrEmpty(sql))
            {
                return null;
            }
            else if (connection == null)
            {
                return null;
            }
            else if (connection.State == ConnectionState.Closed)
            {
                return null;
            }

            List<T> retLis = new List<T>();

            //获取映射关系
            EntityMapping entityMapping = GetEntityTypeInfo(typeof(T));

            //设置属性
            SQLiteCommand command = new SQLiteCommand();
            command.Connection = connection;
            command.CommandType = CommandType.Text;
            command.CommandText = sql;

            //获取数据
            try
            {
                System.Data.SQLite.SQLiteDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    T entity = (T)Activator.CreateInstance(entityMapping.type);
                    ReaderToObject(reader, entity, entityMapping);
                    retLis.Add(entity);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                return null;
            }

            return retLis;
        }



        /// <summary>
        /// 通过主键键值获取T
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public T GetEntityByKey<T>(object keyValue)
        {
            if (keyValue == null)
            {
                return default(T);
            }

            //获取映射关系
            EntityMapping entityMapping = GetEntityTypeInfo(typeof(T));

            //判断主键
            if (string.IsNullOrEmpty(entityMapping.keyName))
            {
                return default(T);
            }

            List<T> retLis = new List<T>();
            string filter = string.Empty;

            filter = string.Format("{0} = @{1}", entityMapping.keyName, entityMapping.keyName);
            Dictionary<string, Object> parameters = new Dictionary<string, object>();
            parameters.Add(entityMapping.keyName, keyValue);
            retLis = Find<T>(filter, string.Empty, parameters);
            if (retLis == null)
            {
                return default(T);
            }
            else if (retLis.Count == 0)
            {
                return default(T);
            }
            else
            {
                return retLis[0];
            }
        }

        /// <summary>
        /// 更新数据，单个实体。

        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Update<T>(T entity)
        {
            if (entity == null)
            {
                return 0;
            }

            List<int> retLis = new List<int>();
            List<T> entLis = new List<T>();

            entLis.Add(entity);
            return Update<T>(entLis);
        }

        /// <summary>
        /// 更新数据，实体列表，支持事务。

        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="flag"></param>
        /// <returns></returns>
        public int Update<T>(List<T> list)
        {
            if (list == null || connection == null)
            {
                return 0;
            }


            if (connection.State == ConnectionState.Closed)
            {
                return 0;
            }

            //获取映射关系
            EntityMapping entityMapping = GetEntityTypeInfo(typeof(T));

            if (string.IsNullOrEmpty(entityMapping.tableName))    //判断表
            {
                return 0;
            }
            else if (string.IsNullOrEmpty(entityMapping.keyName)) //判断主键
            {
                return 0;
            }

            //设置属性
            int updateCount = 0;
            List<string> sqls = new List<string>();
            foreach (T entity in list)
            {
                object keyValue;
                StringBuilder sbSql = new StringBuilder();
                PropertyInfo[] propertyInfos = entity.GetType().GetProperties();
                Dictionary<string, object> parameters = new Dictionary<string, object>();

                sbSql.Append(" update [");
                sbSql.Append(entityMapping.tableName.Trim());
                sbSql.Append("] set ");
                foreach (PropertyInfo pInfo in propertyInfos)
                {

                    //获取实体对应的属性的信息,没有设置则认为不对应任何数据库中字段
                    if (!entityMapping.ColumnMappingDic.ContainsKey(pInfo.Name.Trim()))
                        continue;

                    ColumnAttribute columnAttribute = entityMapping.ColumnMappingDic[pInfo.Name.Trim()];
                    string filedName =  columnAttribute.ColumnName ;

                    //获取对应字段的内容，需判断是否需要加密
                    object value ;
                    if (columnAttribute.IsEncryption )
                    {
                        value = Encrypt((string)pInfo.GetValue(entity, null), key);
                    }
                    else
                    {
                        value = pInfo.GetValue(entity, null);
                    }

                    //记录主键内容
                    if (filedName == entityMapping.keyName)
                    {
                        keyValue = value;
                        //parameters.Add(keyName, keyValue);
                    }

                    parameters.Add(filedName, value);

                    sbSql.Append(filedName + "= @" + filedName + ',');
                }


                updateCount += ExecuteSql(sbSql.ToString().Trim(',') + " where " + entityMapping.keyName + " = @" + entityMapping.keyName, parameters);
            }

            return updateCount;
        }

        /// <summary>
        /// 查找数据，支持设置过滤条件及排序条件。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filter"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public List<T> Find<T>(string filter, string sort)
        {
            List<T> retLis = new List<T>();

            retLis = Find<T>(filter, sort, new Dictionary<string, object>());
            if (retLis == null)
            {
                return new List<T>(); ;
            }

            return retLis;
        }

        public T GetFirstEntity<T>(string filter, string sort)
        {
            List<T> allEntitys = Find<T>(filter, sort);
            if (allEntitys.Count > 0)
            {
                return allEntitys[0];
            }
            else
            {
                return default(T); ;
            }
        }

        public T GetFirstEntity<T>(string filter, string sort, Dictionary<string, object> parameters)
        {
            List<T> allEntitys = Find<T>(filter, sort,parameters);
            if (allEntitys.Count > 0)
            {
                return allEntitys[0];
            }
            else
            {
                return default(T); ;
            }
        }

        /// <summary>
        /// 查找数据，支持设置过滤条件，排序条件及参数化。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filter"></param>
        /// <param name="sort"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public List<T> Find<T>(string filter, string sort, Dictionary<string, object> parameters)
        {
            if (connection == null)
            {
                return null;
            }
            if (connection.State == ConnectionState.Closed)
            {
                return null;
            }

            List<T> retLis = new List<T>();

            //获取映射关系
            EntityMapping entityMapping = GetEntityTypeInfo(typeof(T));

            //定义SQL
            string sql = string.Format("select * from {0} ", entityMapping.tableName);
            if (!string.IsNullOrEmpty(filter))
            {
                sql += string.Format("where {0} ", filter);
            }
            if (!string.IsNullOrEmpty(sort))
            {
                sql += string.Format("order by {0}", sort);
            }

            //设置Command的属性
            SQLiteCommand cmd = connection.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            CreateParameters(cmd,parameters);

            //获取数据
            try
            {
                System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    T entity = (T)Activator.CreateInstance(entityMapping.type);
                    ReaderToObject(reader, entity, entityMapping);
                    retLis.Add(entity);                  
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                return null;
            }

            return retLis;
        }

        /// <summary>
        /// 切换数据源。
        /// </summary>
        /// <param name="dataSourceName"></param>
        /// <returns></returns>
        public bool ChangeDataSourceConn(string dataSourceName)
        {
            if (dataSourceName == currentDB)
                return false;

            if (string.IsNullOrEmpty(dataSourceName))
            {
                return false ;
            }

            if (connectionCache.ContainsKey(dataSourceName))
            {
                connection = connectionCache[dataSourceName].connection;
                currentDB = dataSourceName;
            }
            else
            {
                //创建新的连接
                if (!CreateNewConnection(dataSourceName))
                {
                    return false;
                }
            }

            //如果事务已经启动，则切换到新数据库时也启动事务
            if ((transactionCacheList != null) && (transactionCacheList.Count > 0))
            {
                BeginTransaction();
            }

            return true;

        }


        /// <summary>
        /// 程序运行期动态设置T映射关系。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="propertyToColumn"></param>
        /// <param name="keyName"></param>
        /// <returns></returns>
        public bool SetEntityMapping<T>(string tableName, Dictionary<string, ColumnAttribute> columnAttributes, string keyName)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                return false;
            }

            Type _type = typeof(T);

            foreach (EntityMapping mapping in mappingList)
            {
                if (_type == mapping.type)
                {
                    mappingList.Remove(mapping);
                    break;
                }
            }

            //向缓存中插入映射关系
            EntityMapping mapp = new EntityMapping();
            mapp.type = _type;
            mapp.tableName = tableName;
            mapp.ColumnMappingDic = columnAttributes;
            mapp.keyName = keyName;
            mappingList.Add(mapp);

            return true;
        }

        /// <summary>
        /// 获取表结构
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private DataTable GetTableStruct(string tableName)
        {
            if (connection == null)
            {
                return null;
            }
            if (connection.State == ConnectionState.Closed)
            {
                return null;
            }

            //设置Command的属性
            SQLiteCommand cmd = connection.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select * from '" + tableName + "' where 1=0";

            //获取数据
            try
            {
                System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
                DataTable tableInfo = reader.GetSchemaTable();
                reader.Close();

                return tableInfo;

            }
            catch (Exception ex)
            {
                return null;
            }

        }

        /// <summary>
        /// 开始事务
        /// </summary>
        /// <returns></returns>
        public bool BeginTransaction()
        {
            //try
            //{
            //    if (transactionCacheList.Count > 0)
            //    {
            //        //如果当前连接已经启动过事务，则不允许再次启动事务
            //        foreach (TransactionCache item in transactionCacheList)
            //        {
            //            if (item.connection == connection)
            //                return false;
            //        }
            //    }

            //    SQLiteTransaction transaction = connection.BeginTransaction();
            //    command.Transaction = transaction;

            //    TransactionCache transactionCache = new TransactionCache();
            //    transactionCache.connection = connection;
            //    transactionCache.command = command;
            //    transactionCache.transaction = transaction;
            //    transactionCacheList.Add(transactionCache);

            //    isTrans = true;
            //    return true;
            //}
            //catch (Exception ex)
            //{
            //    return false;
            //}

            return false;

        }

        /// <summary>
        /// 提交事务
        /// </summary>
        /// <returns></returns>
        public bool CommitTransaction()
        {
            if (transactionCacheList.Count == 0)
            {
                return false ;
            }

            try
            {
                //顺序遍历所有缓存的事务，全部提交，如果不是当前的连接则关闭           
                for (int i = 0; i < transactionCacheList.Count; i++)
                {

                    //事务提交
                    transactionCacheList[i].transaction.Commit();

                }

                transactionCacheList.Clear();
            }
            catch (Exception ex)
            {

                return false;
            }

            isTrans = false;
            return true;
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        /// <returns></returns>
        public bool RollbackTransaction()
        {
            if (transactionCacheList.Count == 0)
            {

                return false;
            }

            try
            {
                //倒序遍历     
                for (int i = transactionCacheList.Count - 1; i >= 0; i--)
                {
                    //事务回滚
                    transactionCacheList[i].transaction.Rollback();

                }

                transactionCacheList.Clear();
            }
            catch (Exception ex)
            {
                return false;
            }

            isTrans = false;
            return true;
        }



    }

    public class EntityMappingAttribute : Attribute
    {
        private string tableName;		//实体对应的存储表名称
        private string columnName;	    //实体属性对应的存储表字段名称

        private bool isKey;			    //实体属性对应的存储表字段是否是主键
        private bool isEncryption;		//实体属性是否需要加密


        public string TableName
        {
            get
            {
                return tableName;
            }
            set
            {
                tableName = value;
            }
        }

        public string ColumnName
        {
            get
            {
                return columnName;
            }
            set
            {
                columnName = value;
            }
        }

        public bool IsKey
        {
            get
            {
                return isKey;
            }
            set
            {
                isKey = value;
            }
        }

        public bool IsEncryption
        {
            get
            {
                return isEncryption;
            }
            set
            {
                isEncryption = value;
            }
        }

        public EntityMappingAttribute()
        {

        }
    }

}
